﻿CREATE OR REPLACE Package Getrecipecriteria Is

	--===============================================================
	-- Package information
	-- This Package is for get Recipe Criteria which used in dynamic recipe
	-- Author: Leon Dong
	-- Last Modify Date : 2008-05-14
	--===============================================================

	--===============================================================
	-- Function and Procedure declarations
	--===============================================================
	Function Getdiethickness(p_Container In Varchar2) Return Varchar2;
	Function Getproductdata2(p_Container In Varchar2, p_Spec In Varchar2)
		Return Varchar2;
	Function Getbottomlid(p_Container   In Varchar2,
												p_Bomname     In Varchar2,
												p_Bomrevision In Varchar2) Return Varchar2;
  --===============================================================
	-- Author: Deng Chao
	-- Last Modify Date : 2009-01-20
  -- Get Wafer P/N info in 6 bit
	--===============================================================
  Function GetWaferPart(p_Container In Varchar2, p_Bomname In Varchar2,	p_BomRevision In Varchar2) Return VARCHAR2;
  
  --===============================================================
  -- Get Wafer P/N info in 4 bit
	--===============================================================
  Function GetWaferPart4bit(p_Container In Varchar2) Return Varchar2;
  
  --===============================================================
	-- Author: Eric Guo
	-- Last Modify Date : 2009-11-26
  -- Get SOLDERPASTE part number
	--===============================================================
  Function GetSolderPastePN(p_Container In Varchar2) Return Varchar2;
  
  --===============================================================
	-- Author: Eric Guo
	-- Last Modify Date : 2010-02-12
  -- Get DAF Vendor Code
	--===============================================================
  FUNCTION GetDAFVendorCode(p_ProductBOMName IN VARCHAR2, 
           p_ProductBOMRevision IN VARCHAR2, p_SpecName IN VARCHAR2) RETURN VARCHAR2;

  --===============================================================
	-- Author: Eric Guo
	-- Last Modify Date : 2010-02-12
  -- Get DIE Thickness value for each step
	--===============================================================
  FUNCTION GetDieThickness(p_ProductBOMName IN VARCHAR2, 
           p_ProductBOMRevision IN VARCHAR2, p_SpecName IN VARCHAR2) RETURN VARCHAR2;
           
  --===============================================================
	-- Author: Eric Guo
	-- Last Modify Date : 2011-04-18
  -- Get Cure Program for 2102, 2183 and 2800 now
	--===============================================================
  function Get_2102_Cure_Program(i_bomName in varchar2, 
           i_bomRev in varchar2, i_pnName in varchar2) return VARCHAR2;
           
           
  --===============================================================
	-- Author: Eric Guo
	-- Last Modify Date : 2010-12-29
  -- Get Cure Program for 2087
	--===============================================================
  function Get_2087_Cure_Program(i_bomName in varchar2, 
           i_bomRev in varchar2, i_pnName in varchar2) return VARCHAR2;
           
           
  --===============================================================
	-- Author: Eric Guo
	-- Last Modify Date : 2010-04-06
  -- Get Auto Mold Program for 3010
	--===============================================================
  function Get_3010_SUBSTRATEPART(i_bomName in varchar2, 
           i_bomRev in varchar2, i_pnName in varchar2, i_Container In Varchar2) return VARCHAR2;                      

  --===============================================================
	-- Author: Eric Guo
	-- Last Modify Date : 2010-05-25
  -- Get -UPG for Die Attach for SQ00184585
	--===============================================================
  function GetDieAttachUPG(i_pnName in varchar2, i_Container In Varchar2) return VARCHAR2;

  --===============================================================
	-- Author: Eric Guo
	-- Last Modify Date : 2010-11-25
  -- Get CURE program for 5058
	--===============================================================
  function Get_5058_CURE(i_pnName in varchar2, i_Container In Varchar2) return VARCHAR2;       
 
  --===============================================================
	-- Author: Feimin Guo
	-- Last Modify Date : 2011-4-22
  -- Get CURE program for 3020
	--===============================================================    
  function get_3020_cure(i_pnName in VARCHAR2) return VARCHAR2;                

  --===============================================================
	-- Author: Eric Guo
	-- Last Modify Date : 2011-05-04
  --     used at recipe criteria DAFTHICKNESS-MEM 
	--===============================================================
  function Get_DAF_Thickness(i_bomName in varchar2, 
           i_bomRev in varchar2, i_pnName in varchar2) return VARCHAR2;                     

  --===============================================================
	-- Author: Eric Guo
	-- Last Modify Date : 2011-05-04
  --     used at recipe criteria DAFVENDOR-2 
	--===============================================================
  function Get_DAF_Vendor(i_bomName in varchar2, 
           i_bomRev in varchar2, i_pnName in varchar2) return VARCHAR2;

  --===============================================================
	-- Author: Eric Guo
	-- Last Modify Date : 2011-05-23
  --     used at 2071 lamination rule
	--===============================================================
  function Get_2071_Lamination(i_pnName in varchar2, i_Container In Varchar2) return VARCHAR2;
           
End;
/
CREATE OR REPLACE Package Body Getrecipecriteria Is

	--===============================================================
	-- Package information
	-- This Package is for get Recipe Criteria which used in dynamic recipe
	-- Author: Leon Dong
	-- Last Modify Date : 2008-05-14
	--===============================================================

	--===============================================================
	-- Function and Procedure declarations
	--===============================================================
	Function Getdiethickness(p_Container In Varchar2) Return Varchar2 Is
		Vthickness Product.Diethickness%Type;
		--Vlottype   Container.Lottype%Type;
	Begin
		--=====================
		-- Get the LotType
		--=====================
    /*
		Select t.Lottype
			Into Vlottype
			From Container t
		 Where t.Containername = p_Container;
     */
	
		--=====================
		-- Return the Thickness by LotType
		--=====================

			--================
			-- Modified by Youliang Dong, 2010-8-30
			-- Xiao Guo Jiang Request Monday, August 30, 2010 3:55 PM
      -- Get the A lot die thickness from the A Lot's PN's DieThickness
			--================
	  
		--If Vlottype = 'A' Then
			--================
			-- Modified by Youliang Dong, 2008-7-14
			-- Peng Gao ask to check the device
			--================
      /*
			Select Distinct b.Diethickness
				Into Vthickness
				From a_Lotwaferrunnumbers a, Container c, Product b
			 Where a.Containerid = c.Containerid
				 And c.Productid = b.Productid
				 And a.Waferrunnumbercategory = 'M'
         and a.fromcontainername is null
         AND B.DIETHICKNESS IS NOT NULL
        /* and c.lottype <> 'A'*/
        /*
				 And Trim(a.Waferrunnumber) In
						 (Select Trim(t.Waferrunnumber)
								From (Select Rownum Rn, a.Waferrunnumber
												From a_Lotwaferrunnumbers a, Container c
											 Where a.Containerid = c.Containerid
                         and a.fromcontainername is null
												 And c.Containername = p_Container) t
							 Where Rn < 2) and rownum < 2
               
				/*	And c.Status = 1*/
				 /*And c.Productid <>
						 (Select Productid
								From Container c
							 Where c.Containername = p_Container)*/
               
--		ELSE
      
			Select b.Diethickness
				Into Vthickness
				From Container c, Product b
			 Where c.productid = b.productid
				 And c.Containername = p_Container;
		--End If;
	
		--================
		-- Modified by Deng Chao, 2008-7-11
		-- Not force set to 3digit
		--================
		--If Vthickness Is Not Null Then
		--  Vthickness := Lpad(Vthickness, 3, '0');
		--End If;
	
		Return(Vthickness);
	Exception
		When Others Then
			Return('Function Getdiethickness->' || Sqlerrm);
	End Getdiethickness;

	Function Getproductdata2(p_Container In Varchar2, p_Spec In Varchar2)
		Return Varchar2 Is
		Vproductdata2 Product.Data2%Type;
		Vdata2        Product.Data2%Type;
		Vdensitycode  Product.Densitycode%Type;
		Vpackage      a_Packagecategory.Packagecategoryname%Type;
	Begin
		Select a.Packagecategoryname, t.Data2, t.Densitycode
			Into Vpackage, Vdata2, Vdensitycode
			From Product t, a_Packagecategory a, Container c
		 Where t.Packagecategoryid = a.Packagecategoryid
			 And c.Productid = t.Productid
			 And c.Containername = p_Container;
	
		If p_Spec = '3030' And Vpackage <> 'MICRO-SD' Then
			Vproductdata2 := Vdata2;
		End If;
	
		If p_Spec = '3030' And Vpackage = 'MICRO-SD' Then
			Vproductdata2 := Vdensitycode;
		End If;
	
		If p_Spec = '3060' Then
			Vproductdata2 := Vdata2;
		End If;
	
		Return(Vproductdata2);
	Exception
		When Others Then
			Return('Function Getproductdata2->' || Sqlerrm);
	End Getproductdata2;

	Function Getbottomlid(p_Container   In Varchar2,
												p_Bomname     In Varchar2,
												p_Bomrevision In Varchar2) Return Varchar2 Is
		Vbottomlid Productbase.Productname%Type;
		Vpackage   a_Packagecategory.Packagecategoryname%Type;
    v_ContainerPN       productbase.productname%TYPE;
    
  CURSOR curWO_BottomLid IS
    SELECT ptb.productname
  FROM container co
  INNER JOIN a_workorderdetail wd on wd.workorderid=co.mfgorderid
  INNER JOIN productbase ptb ON ptb.productname=wd.materialpart
  INNER JOIN product pt ON pt.productbaseid=ptb.productbaseid
  INNER JOIN a_materialtype mt ON mt.materialtypeid=pt.materialtypeid
  WHERE co.containername=p_Container
    AND mt.materialtypename='BOTTOMLID'
    AND ptb.revofrcdid=pt.productid;
    
  CURSOR curCo_PN IS
  SELECT ptb.productname
  FROM container co
  INNER JOIN product pt ON pt.productid=co.productid
  INNER JOIN productbase ptb ON ptb.productbaseid=pt.productbaseid
  WHERE co.containername=p_Container
    AND ptb.revofrcdid=pt.productid;
    
	BEGIN
-- Try fetch Bottom Lit in Work order step first
IF p_Container NOT LIKE 'T%' THEN
  OPEN curWO_BottomLid;
  LOOP
    EXIT WHEN curWO_BottomLid%NOTFOUND;
    FETCH curWO_BottomLid INTO Vbottomlid;
  END LOOP;
  CLOSE curWO_BottomLid;
END IF;
      
IF Vbottomlid IS NOT NULL THEN
  Vbottomlid := Substr(Vbottomlid,7);
ELSE
  OPEN curCo_PN;
  LOOP
    EXIT WHEN curCo_PN%NOTFOUND;
    FETCH curCo_PN INTO v_ContainerPN;
  END LOOP;
  CLOSE curCo_PN;

  IF v_ContainerPN = 'SDPLSDB-002G-S' THEN
    Vbottomlid := '13.5X20.95';
  ELSE
		Select Substr(Prdb.Productname, 7)
			Into Vbottomlid
			From a_Productbommateriallist Ml,
					 Product                  Prd,
					 a_Materialtype           Mt,
					 Productbase              Prdb
		 Where Ml.Productbomid =
					 (Select p.Productbomid
							From a_Productbom p, a_Productbombase Pb
						 Where p.Productbombaseid = Pb.Productbombaseid
							 And Pb.Productbomname = p_Bomname
							 And p.Revision = p_Bomrevision)
			 And Ml.Materialpartid = Prd.Productid
			 And Prd.Materialtypeid = Mt.Materialtypeid
			 And Mt.Materialtypename = 'BOTTOMLID'
			 And Prd.Productbaseid = Prdb.Productbaseid;
   END IF;
END IF;
	
		Select a.Packagecategoryname
			Into Vpackage
			From Product t, a_Packagecategory a, Container c
		 Where t.Packagecategoryid = a.Packagecategoryid
			 And c.Productid = t.Productid
			 And c.Containername = p_Container;
	
		If Vpackage = 'MS-PROHG' Then
			Vbottomlid := Substr(Vbottomlid, 1, 5);
		End If;
	
		Return(Vbottomlid);
	Exception
		When Others Then
			Return('Function Getbottomlid->' || Sqlerrm);
	End Getbottomlid;
  
  --===============================================================
	-- Author: Deng Chao
	-- Last Modify Date : 2009-01-20
  -- Get Wafer P/N info
	--===============================================================
  Function GetWaferPart(p_Container In Varchar2, p_Bomname In Varchar2,	p_BomRevision In Varchar2) Return Varchar2 Is
		VwaferPart    a_lotattributes.waferpart%Type;
    VauxWaferPart a_lotattributes.waferpart%Type;
		VmaterialType A_MaterialType.Materialtypename%Type;
	Begin
    SELECT conattr.WAFERPART, mtype.MATERIALTYPENAME
      INTO VwaferPart, VmaterialType
      FROM container con, a_lotattributes conattr, PRODUCT prd, A_MaterialType mtype
     WHERE con.containername = p_Container
       AND con.containerid = conattr.containerid
       AND con.PRODUCTID = prd.PRODUCTID
       AND prd.MATERIALTYPEID = mtype.MATERIALTYPEID;
	
    --===============================================================
	  -- Check if need extra info from wafer P/N, get number only
	  --===============================================================
    IF LENGTH(VwaferPart) > 11 THEN
      SELECT TRIM(TRANSLATE(SUBSTR(VwaferPart, 13, 2),
                            'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz*',
                            ' '))
        INTO VauxWaferPart
        FROM DUAL;
    END IF;
	
		If VmaterialType = 'MIRRORDIE' Then
			VwaferPart := SUBSTR(VwaferPart, 8, 7);
		ELSIF LENGTH(VwaferPart) = 11 OR (LENGTH(VwaferPart) > 11 AND VauxWaferPart is null) THEN
      IF p_Bomname LIKE '%WBC' THEN
			    VwaferPart := SUBSTR(VwaferPart, 8, 4) || '-20';
      ELSE
			    VwaferPart := SUBSTR(VwaferPart, 8, 4) || '-00';
      END IF;
    ELSIF LENGTH(VwaferPart) > 11 AND LENGTH(VauxWaferPart) = 1 THEN
      VwaferPart := SUBSTR(VwaferPart, 8, 5) || '0' || VauxWaferPart;
    ELSIF LENGTH(VwaferPart) > 11 AND LENGTH(VauxWaferPart) = 2 THEN
      IF SUBSTR(VauxWaferPart,2,1) = '-' THEN 
      VwaferPart := SUBSTR(VwaferPart, 8, 5) ||'0' || SUBSTR(VauxWaferPart,1,1);
      ELSE
      VwaferPart := SUBSTR(VwaferPart, 8, 5) || VauxWaferPart;
      END IF;
		End If;
	
		Return(VwaferPart);
	Exception
		When Others Then
			Return('Function GetWaferPart->' || Sqlerrm);
	End GetWaferPart;

  -- Get Wafer P/N info in 4 bit, copy from GetWaferPart, used for 2040 only currently
  Function GetWaferPart4bit(p_Container In Varchar2) Return Varchar2 Is
		VwaferPart    a_lotattributes.waferpart%Type;
    VauxWaferPart a_lotattributes.waferpart%Type;
		VmaterialType A_MaterialType.Materialtypename%Type;
	Begin
    SELECT conattr.WAFERPART, mtype.MATERIALTYPENAME
      INTO VwaferPart, VmaterialType
      FROM container con, a_lotattributes conattr, PRODUCT prd, A_MaterialType mtype
     WHERE con.containername = p_Container
       AND con.containerid = conattr.containerid
       AND con.PRODUCTID = prd.PRODUCTID
       AND prd.MATERIALTYPEID = mtype.MATERIALTYPEID;
	
    --===============================================================
	  -- Check if need extra info from wafer P/N, get number only
	  --===============================================================
    IF LENGTH(VwaferPart) > 11 THEN
      SELECT TRIM(TRANSLATE(SUBSTR(VwaferPart, 13, 2),
                            'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz*',
                            ' '))
        INTO VauxWaferPart
        FROM DUAL;
    END IF;
	
    -- if waferPart is Memory, then only 4 bit needed
    IF substr(VwaferPart,1,5) = '54-81' OR substr(VwaferPart,1,5) = '60-81'  THEN
      VwaferPart := SUBSTR(VwaferPart, 8, 4);    
    ELSE
      If VmaterialType = 'MIRRORDIE' Then
        VwaferPart := SUBSTR(VwaferPart, 8, 7);
      ELSIF LENGTH(VwaferPart) = 11 OR (LENGTH(VwaferPart) > 11 AND VauxWaferPart is null) THEN
        VwaferPart := SUBSTR(VwaferPart, 8, 4) || '-00';
      ELSIF LENGTH(VwaferPart) > 11 AND LENGTH(VauxWaferPart) = 1 THEN
        VwaferPart := SUBSTR(VwaferPart, 8, 5) || '0' || VauxWaferPart;
      ELSIF LENGTH(VwaferPart) > 11 AND LENGTH(VauxWaferPart) = 2 THEN
        IF SUBSTR(VauxWaferPart,2,1) = '-' THEN 
        VwaferPart := SUBSTR(VwaferPart, 8, 5) ||'0' || SUBSTR(VauxWaferPart,1,1);
        ELSE
        VwaferPart := SUBSTR(VwaferPart, 8, 5) || VauxWaferPart;
        END IF;
      End If;
    END IF;
	
		Return(VwaferPart);
	Exception
		When Others Then
			Return('Function GetWaferPart4bit->' || Sqlerrm);
	End GetWaferPart4bit;
  
  -- Get SOLDERPASTE part number
  Function GetSolderPastePN(p_Container In Varchar2) Return Varchar2 IS
  		V_SolderPastePN    productbase.productname%TYPE;
	BEGIN
SELECT mat_ptb.productname INTO V_SolderPastePN
FROM container co
INNER JOIN product pt ON co.productid=pt.productid
INNER JOIN A_ProductBOMList bomlist ON bomlist.productid=pt.productid
INNER JOIN A_ProductBOMMaterialList matlist ON matlist.productbomid=bomlist.bomid
INNER JOIN product mat_pt ON matlist.materialpartid=mat_pt.productid
INNER JOIN productbase mat_ptb ON mat_ptb.productbaseid=mat_pt.productbaseid
INNER JOIN a_materialtype mt ON mat_pt.materialtypeid=mt.materialtypeid
WHERE mt.materialtypename='SOLDERPASTE'
  AND co.containername=p_Container;
    
		Return(V_SolderPastePN);
	EXCEPTION  
		When Others Then
			Return('Function GetSolderPastePN->' || Sqlerrm);
	End GetSolderPastePN;
  
FUNCTION GetDAFVendorCode(p_ProductBOMName IN VARCHAR2, 
         p_ProductBOMRevision IN VARCHAR2, p_SpecName IN VARCHAR2) RETURN VARCHAR2 IS
CURSOR CURVENDORCODE(ci_SpecName varchar2) IS
select mat_mat_vd.vendorcode
from a_productbombase pbb 
INNER JOIN a_productbom pb ON pbb.PRODUCTBOMBASEID = pb.PRODUCTBOMBASEID
INNER JOIN a_productbommateriallist matlist ON pb.PRODUCTBOMID = matlist.PRODUCTBOMID
INNER JOIN spec sp ON matlist.SPECID = sp.SPECID
INNER JOIN specbase spb ON sp.SPECBASEID = spb.SPECBASEID
INNER JOIN product pt_mat ON matlist.MATERIALPARTID = pt_mat.PRODUCTID
INNER JOIN productbase ptb_mat ON ptb_mat.productbaseid=pt_mat.productbaseid
INNER JOIN a_materialtype mt ON pt_mat.MATERIALTYPEID = mt.MATERIALTYPEID
INNER JOIN a_productbomlist mat_bomlist ON pt_mat.PRODUCTID = mat_bomlist.PRODUCTID
INNER JOIN a_productbommateriallist mat_matlist ON mat_bomlist.BOMID = mat_matlist.PRODUCTBOMID
INNER JOIN product mat_mat ON mat_matlist.MATERIALPARTID = mat_mat.PRODUCTID
INNER JOIN productbase mat_matb ON mat_matb.productbaseid=mat_mat.productbaseid
INNER JOIN a_materialtype mat_mat_mt ON mat_mat.MATERIALTYPEID = mat_mat_mt.MATERIALTYPEID
INNER JOIN vendor mat_mat_vd ON mat_mat.vendorid = mat_mat_vd.vendorid
where pbb.PRODUCTBOMNAME = p_ProductBOMName
  and pb.REVISION = p_ProductBOMRevision
  and spb.SPECNAME = ci_SpecName      
  and (mt.MATERIALTYPENAME like 'CONTROLLER%' or mt.MATERIALTYPENAME like 'MEMORY%' 
      or mt.MATERIALTYPENAME like 'CHARGEBUMP%' or mt.MATERIALTYPENAME like 'MIRROR%' 
      or mt.MATERIALTYPENAME like 'SWITCHER%' or mt.MATERIALTYPENAME like 'DDRDIE%')      
  and mat_mat_mt.MATERIALTYPENAME = 'DAF';
           
V_VendorCode         vendor.vendorcode%TYPE;
BEGIN

IF p_ProductBOMName IN ('0G1181-00251-2-NI20*6-INAND32G',
  '0G2181-00251-1-NI20*6-INAND32G',
  '0G1181-00123-1-NI20*6-2-05013-032G',
  '0G2181-00251-1-NI20*6-06163-032G',
  '0G2181-00251-1-NI20*6-06102-032G',
  '0G2181-00251-1-NI20*6-06351-064G') THEN
  V_VendorCode := 'NI';
END IF;  

-- Try fetch current step first
OPEN CURVENDORCODE(p_SpecName);
LOOP
  EXIT WHEN CURVENDORCODE%NOTFOUND;
  FETCH CURVENDORCODE INTO V_VendorCode;
END LOOP;
CLOSE CURVENDORCODE;
   
-- If not find, try get from 2072
IF V_VendorCode IS NULL THEN
  OPEN CURVENDORCODE('2072');
  LOOP
    EXIT WHEN CURVENDORCODE%NOTFOUND;
    FETCH CURVENDORCODE INTO V_VendorCode;
  END LOOP;
  CLOSE CURVENDORCODE;
END IF;

-- If not find, try get from 2172
IF V_VendorCode IS NULL THEN
  OPEN CURVENDORCODE('2172');
  LOOP
    EXIT WHEN CURVENDORCODE%NOTFOUND;
    FETCH CURVENDORCODE INTO V_VendorCode;
  END LOOP;
  CLOSE CURVENDORCODE;
END IF;

IF p_ProductBOMName IN ('0G1162-07807-002G-WBC') AND p_SpecName = '2071' THEN
  V_VendorCode := 'HE';
END IF;  

Return(V_VendorCode);
END GetDAFVendorCode;

FUNCTION GetDieThickness(p_ProductBOMName IN VARCHAR2, 
         p_ProductBOMRevision IN VARCHAR2, p_SpecName IN VARCHAR2) RETURN VARCHAR2 IS
CURSOR CURDieThickNess(ci_SpecName varchar2) IS
select pt_mat.diethickness 
 FROM a_productbombase pbb
INNER JOIN a_productbom pb ON pbb.PRODUCTBOMBASEID = pb.PRODUCTBOMBASEID
INNER JOIN a_productbommateriallist matlist ON pb.PRODUCTBOMID = matlist.PRODUCTBOMID
INNER JOIN spec sp ON matlist.SPECID = sp.SPECID
INNER JOIN specbase spb ON sp.SPECBASEID = spb.SPECBASEID
INNER JOIN product pt_mat ON matlist.MATERIALPARTID = pt_mat.PRODUCTID
INNER JOIN a_materialtype mt ON pt_mat.MATERIALTYPEID = mt.MATERIALTYPEID
INNER JOIN a_productbomlist mat_bomlist ON pt_mat.PRODUCTID = mat_bomlist.PRODUCTID
INNER JOIN a_productbommateriallist mat_matlist ON mat_bomlist.BOMID = mat_matlist.PRODUCTBOMID
 where pbb.PRODUCTBOMNAME = p_ProductBOMName   
   and pb.REVISION = p_ProductBOMRevision
   and spb.SPECNAME = ci_SpecName
   and (mt.MATERIALTYPENAME like 'CONTROLLERDIE%'
       or mt.MATERIALTYPENAME like 'MEMORYDIE%' 
       or mt.MATERIALTYPENAME like 'CHARGEBUMPDIE%'
       OR mt.MATERIALTYPENAME like 'MIRRORDIE%'
       OR mt.MATERIALTYPENAME like 'DDRDIE%'
       OR mt.MATERIALTYPENAME like 'SWITCHERDIE%');
           
V_DieThickNess         product.thickness%TYPE;
BEGIN
-- Hard code Die Thickness as there is no way to trace back for current step material die thickness
IF p_ProductBOMName IN ('0G1181-00251-2-NI20*6-INAND32G',
  '0G2181-00251-1-NI20*6-INAND32G',
  '0G1181-00123-1-NI20*6-2-05013-032G',
  '0G2181-00251-1-NI20*6-06163-032G',
  '0G2181-00251-1-NI20*6-06102-032G',
  '0G2181-00251-1-NI20*6-06351-064G') THEN
  V_DieThickNess := 152;
END IF;  

-- Try fetch current step first
OPEN CURDieThickNess(p_SpecName);
LOOP
  EXIT WHEN CURDieThickNess%NOTFOUND;
  FETCH CURDieThickNess INTO V_DieThickNess;
END LOOP;
CLOSE CURDieThickNess;
   
-- If not find, try get from 2072
IF V_DieThickNess IS NULL THEN
  OPEN CURDieThickNess('2072');
  LOOP
    EXIT WHEN CURDieThickNess%NOTFOUND;
    FETCH CURDieThickNess INTO V_DieThickNess;
  END LOOP;
  CLOSE CURDieThickNess;
END IF;
   
-- If not find, try get from 2172
IF V_DieThickNess IS NULL THEN
  OPEN CURDieThickNess('2172');
  LOOP
    EXIT WHEN CURDieThickNess%NOTFOUND;
    FETCH CURDieThickNess INTO V_DieThickNess;
  END LOOP;
  CLOSE CURDieThickNess;
END IF;
   
Return(V_DieThickNess);
END GetDieThickness;

-- USED IN 2102, 2183 AND 2800 CURE WHICH USED AFTER 2078
function Get_2102_Cure_Program(i_bomName in varchar2, i_bomRev in varchar2, i_pnName in varchar2)
return VARCHAR2 IS
  Result varchar2(40);  
  v_packageCategory a_packagecategory.packagecategoryname%TYPE;
  v_vendorCode vendor.vendorcode%TYPE;
  
  v_DieQty product.dieqty%TYPE;
  v_Technology product.technology%TYPE;
  v_DAFname productbase.productname%TYPE;
BEGIN
  SELECT pc.packagecategoryname, pt.dieqty, pt.technology
  INTO v_packageCategory, v_DieQty, v_Technology
  FROM product pt
  INNER JOIN productbase ptb ON ptb.productbaseid=pt.productbaseid
  INNER JOIN a_packagecategory pc ON pc.packagecategoryid=pt.packagecategoryid
  WHERE ptb.revofrcdid = pt.productid
    AND ptb.productname = i_pnName;

  ---add by feimin 20110324  'Q-54-82-06417-008G'   
  --add by feimin 20110628  '54-82-05105-032G','54-82-05105-016G'
  IF i_pnName IN ('Q-54-82-05102-008G','Q-54-82-06439-002G','54-82-05105-032G','54-82-05105-016G','54-82-06472-032G','Q-54-82-05068-064G','Q-54-82-07027-032G','54-82-05105-064G','Q-54-82-05076-064G') THEN
    RESULT := 'PATN 2 High pressure oven';
  ELSIF i_pnName IN ('54-82-05010-016G','Q-54-82-06417-008G','Q-54-82-06417-008G-NIP','Q-54-82-06417-008G') THEN
    RESULT := 'PATN 7 High pressure oven';
  ELSIF i_pnName IN ('54-62-06924-008G') THEN
    RESULT := '12';
  ELSIF i_pnName IN ('54-62-06074-002G','54-62-06538-004G',
'54-62-06071-002G',
'54-62-06072-002G',
'54-62-06073-002G', '54-82-06439-002G', '54-82-06397-002G', '54-82-06478-002G',
'Q-54-62-04580-004G',
'Q-54-62-04580-008G',
'Q-54-62-04667-004G',
'Q-54-62-05625-001G',
'Q-54-62-06042-002G',
'Q-54-62-06057-004G',
'Q-54-62-06057-008G',
'Q-54-62-06071-002G',
'Q-54-82-03488-002G',
'Q-54-82-03488-004G',
'Q-54-82-04147-004G',
'Q-54-82-04615-004G',
'Q-54-82-04615-008G'
 ) THEN
    RESULT := '6';
  ELSIF i_bomName IN ('0G1162-07807-002G-WBC') THEN
    RESULT := '5'; 
  ELSIF (v_DieQty = 8 AND v_Technology LIKE '32%')
    OR (v_packageCategory LIKE 'INAND%') --SQ00351085
    OR (v_packageCategory LIKE 'ISSD%')
    OR (v_packageCategory LIKE 'ESSD%')
    OR (v_packageCategory LIKE 'UBGA%')
    OR (v_packageCategory LIKE 'ULGA%')
    OR (v_packageCategory LIKE 'MCP%')
    OR (i_pnName IN 
    -- 8 PN is hard code in SPAS SQ00163028
    ('54-82-03293-016G','54-82-03365-016G','54-82-03476-016G','54-82-03482-016G',
     '54-82-03512-016G','54-82-03793-016G','54-82-04308-016G','54-82-04600-016G',
     'Q-54-82-05101-016G',
    -- 1 PN is hard code in SPAS SQ00175286
    --54-82-05013-032G	SQ00175515
    --54-82-05006-032G	SQ00175502
    --54-82-05008-032G	SQ00175552
     '54-82-05000-032G','54-82-05013-032G','54-82-05006-032G','54-82-05008-032G',
    -- 6 PN is hard code in SPAS SQ00178765 
     '54-82-03876-032G','54-82-05008-032GE','Q-54-82-03878-032G','Q-54-82-05006-032G','Q-54-82-05008-032G','Q-54-82-03894-032G',
    -- 1 PN is hard code in SPAS SQ00190584
     'Q-54-82-06146-002G',
    -- 1 PN is hard code in SPAS SQ00191669
     '54-82-05006-016G',
    -- 1 PN is hard code in SPAS SQ00192423
     'Q-54-82-06144-004G',
    -- 1 PN is hard code in SPAS SQ00192469,SQ00192458,SQ00192444
     'Q-54-82-06145-016G','Q-54-82-06145-008G','Q-54-82-06144-016G',
     -- Pn is hard code in SPAS SQ00192437 Q-54-82-06144-008G, SQ00192345 Q-54-82-06145-004G
     'Q-54-82-06144-008G','Q-54-82-06145-004G',
     -- 1 PN is hard code in SPAS SQ00195647
     'Q-54-82-06142-004G',
     -- 1 PN is hard code in SPAS SQ00196058
     'Q-54-82-05016-MS',
     -- 2 PN is hard code in SPAS SQ00197110
     'Q-54-82-03894-032G-SD','Q-54-82-06102-032G',
     -- 1 PN is hard code in SPAS SQ00196769
     '54-82-03894-032GE',
     -- 1 PN is hard code in SPAS SQ00197202
     '54-82-05001-032G',
     -- 1 PN is hard code in SPAS SQ00197055
     'Q-54-82-05016-016G',
     -- 1 PN is hard code in SPAS SQ00197202
     '54-82-05001-032G',
     -- REQ By Leon Li @ 2010-07-02
     '54-82-05022-016G','54-82-05022-032G',
      --SQ00197175 Q-54-82-05016-016G-BA
      --SQ00197165 Q-54-82-05016-032G
      --SQ00197196 Q-54-82-05016-032G-BA
      'Q-54-82-05016-016G-BA','Q-54-82-05016-032G','Q-54-82-05016-032G-BA',
      --SQ00196609
      '54-82-004314-032G','54-82-05648-032G',
      --SQ00200799	Q-54-82-05009-016G
      --SQ00200815	Q-54-82-05009-032G
      'Q-54-82-05009-016G','Q-54-82-05009-032G',
      --SQ00202999 54-62-03894-032GE
      '54-62-03894-032GE',
      --SQ00203253	54-82-06146-002G
      '54-82-06146-002G',
      --SQ00203266	54-82-06144-004G
      '54-82-06144-004G',
      --SQ00203279	54-82-06144-008G       SQ00203285	54-82-06144-016G
      '54-82-06144-008G','54-82-06144-016G',
      --SQ00204986	Q-54-82-06143-004G
      'Q-54-82-06143-004G',
      --SQ00205011	Q-54-82-06143-016G
      'Q-54-82-06143-016G',
      --SQ00205000	Q-54-82-06143-008G
      'Q-54-82-06143-008G','54-82-06143-008G',
      --SQ00204903	                       Q-54-82-06142-016G
      --SQ00204899  	                     Q-54-82-06142-008G
      --SQ00205011	                       Q-54-82-06143-016G
      'Q-54-82-06142-016G','Q-54-82-06142-008G','Q-54-82-06143-016G',
      '54-82-05028-016G','54-82-05028-032G',  -- req by Leon Li by eMail 7/27/2010
      --SQ00205301	54-82-06147-002G
      '54-82-06147-002G',
      --SQ00209152	54-62-06164-008GE
      --SQ00209157	54-62-06164-016GE      
      '54-62-06164-008GE','54-62-06164-016GE',
      --SQ00209162	54-62-06164-004GE
      '54-62-06164-004GE',
      --SQ00210561	Q-54-82-06184-004G
      --SQ00210619	Q-54-82-06183-008G
      'Q-54-82-06184-004G','Q-54-82-06183-008G',
      --SQ00211098	54-82-06164-016GE
      --SQ00211093	54-82-06164-008GE
      --SQ00211087	54-82-06164-004GE
      '54-82-06164-016GE','54-82-06164-008GE','54-82-06164-004GE',
      --SQ00213354	Q-54-82-05003-032G
      'Q-54-82-05003-032G',
      --SQ00214111 54-82-06184-004G
      '54-82-06184-004G',
      --SQ00216353	54-82-06152-032G
      '54-82-06152-032G',
      --SQ00218089	54-82-06142-008G          SQ00218091	54-82-06142-016G
      '54-82-06142-008G','54-82-06142-016G',
      --Lisa EMail Fri 9/10/2010 9:44 AM
      'Q-54-82-05917-064G',
      --SQ00222542	54-82-06183-004G
      --SQ00222212	54-82-06183-008G
      '54-82-06183-004G','54-82-06183-008G',
      --SQ00223482 54-82-06200-004G 
--SQ00223480 54-82-06199-008G 
--SQ00223489 54-82-06200-008G 
--SQ00223473 54-82-06199-004G 
--SQ00223531 Q-54-82-06200-008G
             '54-82-06200-004G','54-82-06199-008G','54-82-06200-008G','54-82-06199-004G','Q-54-82-06200-008G',
             --SQ00228427	Q-54-82-05036-032G
             'Q-54-82-05036-032G',
--SQ00228423	Q-54-82-05036-016G
             'Q-54-82-05036-016G',
             --SQ00243011	54-82-06145-004GE	SQ00243017	54-82-06145-008GE
             '54-82-06145-004GE','54-82-06145-008GE',
             --SQ00243041	54-82-06102-032GE
--SQ00243027	54-82-06145-016GE
             '54-82-06102-032GE','54-82-06145-016GE',
--SQ00245989	54-82-06227-008G
--SQ00245992	54-82-06227-004G
              '54-82-06227-008G','54-82-06227-004G',
              --SQ00246555  54-82-06143-004G
              '54-82-06143-004G',
              --SQ00247341
              'Q-54-82-06339-004G',
              --SQ00246570	Q-54-82-06175-002G
              'Q-54-82-06175-002G',
              'Q-54-82-06181-064G', -- SQ00253742
              --SQ00255566	Q-54-82-06181-032G
              'Q-54-82-06181-032G',
              --SQ00259770	Q-54-82-06181-008G
              'Q-54-82-06181-008G',
              --SQ00259805	Q-54-82-06181-016G
              'Q-54-82-06181-016G',
              --SQ00261855
              '54-82-06351-064GE',
              --SQ00271008	54-82-06145-016G
              '54-82-06145-016G',
              'Q-54-82-06439-002G','54-82-05000-016G'
           ))
        THEN
          RESULT := 'PATN 2 High pressure oven';
        ELSIF i_pnName IN ('54-82-05010-008G',
      --SQ00227894             
      'Q-54-82-05100-016G','Q-54-82-05100-032G','Q-54-82-05100-064G'      
      )
      THEN
        RESULT := 'PATN 6 High pressure oven';        
  ELSE
     SELECT DISTINCT ve.vendorcode, ptb.productname INTO v_vendorCode, v_DAFname
     FROM A_ProductBomMaterialList ml
        INNER JOIN Product prd ON ml.MaterialPartId = prd.ProductId
        INNER JOIN Vendor ve ON prd.vendorid=ve.vendorid
        INNER JOIN A_MaterialType mt ON prd.MaterialTypeId = mt.MaterialTypeId
        INNER JOIN productbase ptb ON ptb.productbaseid=prd.productbaseid
     WHERE ml.productbomid = 
      ( SELECT DISTINCT p.productbomid FROM A_ProductBom p, A_ProductBomBase pb, product pd
        WHERE P.ProductBomBaseId = pb.ProductBomBaseId 
          AND pb.ProductBomName = i_bomName
          AND p.Revision = i_bomRev
          and pd.device = i_pnName)
     AND mt.MaterialTypeName = 'DAF';    
  
    IF v_packageCategory = 'UBGA' OR v_packageCategory = 'ULGA' OR
       v_packageCategory = '104LGA' OR v_packageCategory = 'INAND2' OR 
       v_packageCategory = 'INAND3' OR v_packageCategory = 'INAND4' THEN       
       IF v_vendorCode = 'HI' THEN
          RESULT := '10';
       ELSE
          RESULT := '8';
       END IF;       
    ELSE
       RESULT := '6';
    END IF;   
    
    
    IF v_DAFname='54-42-00116' THEN
       RESULT := '12';
    END IF;
    IF i_pnName='54-82-05100-016G' THEN
      RESULT := '2';
    END IF;
  END IF;
  return(Result);
end Get_2102_Cure_Program;

function Get_2087_Cure_Program(i_bomName in varchar2, i_bomRev in varchar2, i_pnName in varchar2)
return VARCHAR2 IS
  Result varchar2(40);  
  v_packageCategory a_packagecategory.packagecategoryname%TYPE;
  v_vendorCode vendor.vendorcode%TYPE;
  
  v_DieQty product.dieqty%TYPE;
  v_Technology product.technology%TYPE;
BEGIN
  SELECT pc.packagecategoryname, pt.dieqty, pt.technology
  INTO v_packageCategory, v_DieQty, v_Technology
  FROM product pt
  INNER JOIN productbase ptb ON ptb.productbaseid=pt.productbaseid
  INNER JOIN a_packagecategory pc ON pc.packagecategoryid=pt.packagecategoryid
  WHERE ptb.revofrcdid = pt.productid
    AND ptb.productname = i_pnName;
    
  -- Add by feimin 20110415 'Q-54-82-05068-032G',
  IF i_pnName in ('Q-54-82-05068-032G') THEN
     RESULT := '6';   
  -- Add by feimin 20110324 'Q-54-82-06407-004G','Q-54-82-06417-008G'
  ELSIF i_pnName in ('Q-54-82-06407-004G','Q-54-82-06417-008G',
  'Q-54-82-06417-008G-NIP','Q-54-82-06417-008G','Q-54-82-07388-004G','Q-54-82-07387-002G') THEN
    RESULT := 'PATN 7 High pressure oven';       
  ELSIF  (v_packageCategory LIKE 'INAND%') 
    OR (i_pnName IN 
    -- 9 PN is hard code in original 2087 rule
    ('54-82-04147-032G','54-82-04147-032G-ULTRA','54-82-08651-064G',
    '54-82-04383-032G','54-82-04385-032G','54-82-05916-032G',
    '54-82-05986-032G','54-82-04382-032G','54-82-04381-032G',
    '54-82-06351-064GE','20-81-00251-1-NI20*6-INAND32G','54-82-06472-032G','Q-54-82-05068-064G',
    '20-81-00251-2-NI20*6-INAND32G'))
  THEN
    RESULT := 'PATN 2 High pressure oven';   
  ELSE
    RESULT := '10';
  END IF;  
  
  return(Result);
end Get_2087_Cure_Program;

function Get_3010_SUBSTRATEPART(i_bomName in varchar2, i_bomRev in varchar2,
         i_pnName in varchar2, i_Container In Varchar2)
return VARCHAR2 IS
CURSOR curWorkOrderSubstrate IS
  SELECT substr(pt.data1,8,5)
  FROM container co
  INNER JOIN a_workorderdetail wd ON wd.workorderid=co.mfgorderid
  INNER JOIN productbase ptb ON ptb.productname=wd.materialpart
  INNER JOIN product pt ON pt.productbaseid=ptb.productbaseid
  INNER JOIN a_materialtype mt ON pt.materialtypeid=mt.materialtypeid
  WHERE co.containername = i_Container
    AND pt.productid=ptb.revofrcdid
    AND mt.materialtypename LIKE 'SUB%';
Result varchar2(40);  
BEGIN
  -- 1 PN is hard code in SPAS SQ00163028
  IF i_pnName IN ('54-82-05000-032G') THEN
    OPEN curWorkOrderSubstrate;
    LOOP
      EXIT WHEN curWorkOrderSubstrate%NOTFOUND;
      FETCH curWorkOrderSubstrate INTO RESULT;
      EXIT;
    END LOOP;
    CLOSE curWorkOrderSubstrate;
  END IF;
  
  IF RESULT IS NULL THEN
    SELECT DISTINCT substr(mat.data1,8,5) INTO RESULT
    FROM a_productbom bom
    INNER JOIN a_productbombase bomb ON bomb.productbombaseid=bom.productbombaseid
    INNER JOIN A_ProductBomMaterialList matlist ON matlist.productbomid=bom.productbomid
    INNER JOIN product mat ON mat.productid=matlist.materialpartid
    INNER JOIN a_materialtype mt ON mt.materialtypeid=mat.materialtypeid
    WHERE mt.materialtypename LIKE 'SUB%'
      AND bomb.productbomname=i_bomName
      AND bom.revision = i_bomRev;
  END IF;
  return(Result);
end Get_3010_SUBSTRATEPART;


function GetDieAttachUPG(i_pnName in varchar2, i_Container In Varchar2)
return VARCHAR2 IS
Result varchar2(40);  
CURSOR curProductInfo IS
SELECT pt.densitycode,pc.packagecategoryname, pt.technology
FROM product pt
INNER JOIN productbase ptb ON ptb.productbaseid=pt.productbaseid
INNER JOIN a_packagecategory pc ON pc.packagecategoryid=pt.packagecategoryid
WHERE pt.productid=ptb.revofrcdid
  AND ptb.productname = i_pnName;
vDensityCode product.densitycode%TYPE;
vPackageCategory a_packagecategory.packagecategoryname%TYPE;
vTechnology  product.technology%TYPE;

CURSOR curDetectContainer2039 IS
SELECT co.containername
FROM container co
INNER JOIN a_scheduledata sd ON sd.scheduledataid=co.scheduledataid
INNER JOIN workflow wf ON sd.wipworkflowid=wf.workflowid
INNER JOIN workflowstep wfs ON wfs.workflowid=wf.workflowid
WHERE co.containername = i_Container
  AND wfs.workflowstepname='2039';
v_Container2039Exist container.containername%TYPE;  

CURSOR curDetectWorkflow2039 IS
SELECT wfs.workflowstepname
FROM workflow wf
INNER JOIN workflowbase wfb on wfb.workflowbaseid=wf.workflowbaseid
INNER JOIN workflowstep wfs ON wfs.workflowid=wf.workflowid
WHERE wf.workflowid=wfb.revofrcdid
  AND wfs.workflowstepname='2039'
  AND wfb.workflowname=i_pnName;
v_Workflow2039Exist workflowstep.workflowstepname%TYPE;
v_LotType container.lottype%TYPE;
  
BEGIN
  OPEN curProductInfo;
  LOOP
    EXIT WHEN curProductInfo%NOTFOUND;
    FETCH curProductInfo INTO vDensityCode,vPackageCategory,vTechnology;
    EXIT;
  END LOOP;
  CLOSE curProductInfo;

  IF vDensityCode = '32G' AND vPackageCategory = 'MICRO-SD' AND vTechnology LIKE '32%' THEN
    OPEN curDetectContainer2039;
    LOOP
      EXIT WHEN curDetectContainer2039%NOTFOUND;
      FETCH curDetectContainer2039 INTO v_Container2039Exist;
      EXIT;
    END LOOP;
    CLOSE curDetectContainer2039;
    
    IF v_Container2039Exist IS NOT NULL THEN
        RESULT := '-UPG';
    ELSE
      SELECT co.lottype INTO v_LotType
      FROM container co
      WHERE co.containername=i_Container;
      
      IF v_LotType IN ('M','F','P','E','Q','T') THEN
        OPEN curDetectWorkflow2039;
        LOOP
          EXIT WHEN curDetectWorkflow2039%NOTFOUND;
          FETCH curDetectWorkflow2039 INTO v_Workflow2039Exist;
          EXIT;
        END LOOP;
        CLOSE curDetectWorkflow2039;
        
        IF v_Workflow2039Exist IS NOT NULL THEN
          RESULT := '-UPG';          
        END IF;
      END IF;
    END IF;
  END IF;
  RETURN (RESULT);
end GetDieAttachUPG;

function Get_5058_CURE(i_pnName in varchar2, i_Container In Varchar2)
return VARCHAR2 IS
CURSOR cur90PrevCard IS
  SELECT DISTINCT wod.materialpart
  FROM container co
  INNER JOIN a_workorderdetail wod ON wod.workorderid=co.mfgorderid
  WHERE wod.materialpart LIKE '54-90%'
    AND co.containername=i_Container;
  
vPurchaseCard a_workorderdetail.materialpart%TYPE;
vPNMarkingSpec A_MarkingSpec.MarkingSpecName%TYPE;
Result varchar2(40);  
BEGIN
  OPEN cur90PrevCard;
  LOOP
    EXIT WHEN cur90PrevCard%NOTFOUND;
    FETCH cur90PrevCard INTO vPurchaseCard;
    EXIT;
  END LOOP;
  CLOSE cur90PrevCard;

  SELECT nvl(nvl(laser_ms.markingspecname,ink_ms.markingspecname),pt.markingspec) INTO vPNMarkingSpec
  FROM product pt
  INNER JOIN productbase ptb ON ptb.productbaseid=pt.productbaseid
  LEFT JOIN a_markingspec laser_ms ON laser_ms.markingspecid=pt.lasermarkingspecid
  LEFT JOIN a_markingspec ink_ms ON ink_ms.markingspecid=pt.inkmarkingspecid
  WHERE ptb.productname=i_pnName;
  --SQ00262589	54-90-16764-002G
  IF vPurchaseCard IN ('54-90-16701-002G','54-90-16702-002G','54-90-16838-002G','54-90-16839-002G',
   '54-90-16202-002G','54-90-16764-002G') THEN
    Result:='3: 150C_2.5hr 5 Layer, KOREA';
  ELSIF vPurchaseCard IN ('54-90-16474-002G','54-90-16606-002G','54-90-16607-002G','54-90-16608-002G', '54-90-16138-002G',
   '54-90-16763-002G','54-90-16653-002G','54-90-16187-002G','54-90-16188-002G','54-90-17373-008G',
   '54-90-16953-008G','54-90-16953-004G','54-90-17938-004G') THEN
    Result:='14: 140C_1.5hr 5 Layer, TAIWAN';
  ELSIF vPNMarkingSpec IN ('54-35-01200','54-35-01292') THEN
    Result:='11: 100C_30min 3 Layer';
  ELSE
    Result:='10: 120C_1.5hr 5 Layer';
  END IF;
  
  return(Result);
end Get_5058_CURE;


-----2_PMC
FUNCTION get_3020_cure(i_pnName in varchar2) 
  RETURN VARCHAR2 IS 
    v_packageCategory a_packagecategory.packagecategoryname%TYPE;
    v_Cellbit product.cellbit%TYPE;
    v_Technology product.technology%TYPE;
    Result varchar2(40);  
  BEGIN
      SELECT pc.packagecategoryname, pt.cellbit, pt.technology
      INTO v_packageCategory, v_Cellbit, v_Technology
      FROM product pt
      INNER JOIN productbase ptb ON ptb.productbaseid=pt.productbaseid
      INNER JOIN a_packagecategory pc ON pc.packagecategoryid=pt.packagecategoryid
      WHERE ptb.revofrcdid = pt.productid
      AND ptb.productname =i_pnName;
      IF v_packageCategory IN('UBGA','MCP','ISSD','104LGA') THEN
        IF i_pnName IN('Q-54-82-05102-008G','Q-54-82-05101-016G','54-82-03077-2048','Q-54-82-07027-004G','Q-54-82-07027-008G','Q-54-82-07027-016G','54-82-05055-016G','54-82-05055-064G','Q-54-82-04900-MECH','Q-54-82-05056-032G','Q-54-82-05056-016G','Q-54-82-05056-064G','Q-54-82-05056-032G','Q-54-82-05056-016G','Q-54-82-05068-064G') OR
          (v_Technology IN('32NM','32nm','43NM','43nm','24NM','24nm') AND v_Cellbit IN('D2','D3','EX3','D2H')) THEN
            RESULT:='2: 175C_6hr';
         ELSE
           RESULT:='3: 175C_24hr';
         END IF;
      ELSIF v_packageCategory IN('INAND MCP','INAND2','INAND3','INAND4','INAND5','ULGA','ESSD') THEN 
           IF v_Technology IN('32NM','32nm','24NM','24nm')  THEN
              RESULT:='2: 175C_6hr';
           ELSIF i_pnName IN('54-82-02561-4096') THEN
              RESULT:='2: 175C_6hr';
           ELSE
              RESULT:='3: 175C_24hr';
           END IF;
      elsif i_pnName IN('Q-54-82-05076-032G','Q-54-82-05076-016G','Q-54-82-05076-008G') THEN
        RESULT:='2: 175C_6hr';
      ELSE
        RESULT:='1: 175C_2hr';
      END IF;
      RETURN(RESULT);
END get_3020_cure;

function Get_DAF_Thickness(i_bomName in varchar2, i_bomRev in varchar2, i_pnName in varchar2)
return VARCHAR2 IS
  Result varchar2(40);  
BEGIN
  
  IF i_bomName ='0G1162-07807-002G-WBC' then
  --OR i_bomName = '0G1162-06074-002G'
  --OR i_bomName = '0G1162-06538-004G'
  --OR i_bomName = '0G1162-06071-002G'
  --OR i_bomName = '0G1162-06072-002G'
  --OR i_bomName = '0G1162-06073-002G' THEN
    RESULT := '20';   -- Hard code thickness since BOM have no DAF
  ELSE
select DISTINCT mat_atv.MATERIALATTRVALUE INTO RESULT
FROM a_productbom bom
INNER JOIN a_productbombase bomb ON bom.productbombaseid=bomb.productbombaseid
INNER JOIN a_productbommateriallist matlist ON matlist.productbomid=bom.productbomid
INNER JOIN product mat ON mat.productid=matlist.materialpartid
INNER JOIN a_materialtype mat_mt ON mat_mt.materialtypeid=mat.materialtypeid
INNER JOIN productbase matb ON matb.productbaseid=mat.productbaseid
INNER JOIN a_materialattrvalue mat_atv ON mat.productid=mat_atv.materialpartid
INNER JOIN a_materialattributes mat_atb ON mat_atb.materialattributesid=mat_atv.materialattributesid
 where bomb.PRODUCTBOMNAME =  i_bomName
   and bom.REVISION = i_bomRev
   and mat_mt.MATERIALTYPENAME = 'DAF'
   and mat_atb.MATERIALATTRIBUTESNAME = 'THICKNESS';
  END IF;  
  
  return(Result);
end Get_DAF_Thickness;

function Get_DAF_Vendor(i_bomName in varchar2, i_bomRev in varchar2, i_pnName in varchar2)
return VARCHAR2 IS
  Result varchar2(40);  
BEGIN
  IF i_bomName ='0G1162-07807-002G-WBC' THEN
    RESULT := 'BC';   -- Hard code vendor code since BOM have no DAF
  --ELSIF i_bomName = '0G1162-06074-002G'-- OR i_bomName = '0G1162-06538-004G'
  --OR i_bomName = '0G1162-06071-002G'
 -- OR i_bomName = '0G1162-06072-002G'
  --OR i_bomName = '0G1162-06073-002G' THEN
  --  RESULT := 'HE'; 
  ELSE
SELECT  ve.vendorcode INTO RESULT
FROM
  A_ProductBomMaterialList ml, Product prd, A_MaterialType mt,Vendor ve
WHERE ml.productbomid = 
  (SELECT p.productbomid FROM A_ProductBom p, A_ProductBomBase pb 
   WHERE P.ProductBomBaseId = pb.ProductBomBaseId 
     AND pb.ProductBomName = i_bomName
     AND p.Revision = i_bomRev)
  AND ml.MaterialPartId = prd.ProductId
  AND prd.MaterialTypeId = mt.MaterialTypeId
  and prd.vendorid=ve.vendorid
  AND mt.MaterialTypeName = 'DAF'
Union
Select 'EP'
FROM
  A_ProductBomMaterialList ml, Product prd, A_MaterialType mt
WHERE ml.productbomid = 
  (SELECT p.productbomid FROM A_ProductBom p, A_ProductBomBase pb 
   WHERE P.ProductBomBaseId = pb.ProductBomBaseId 
     AND pb.ProductBomName = i_bomName
     AND p.Revision = i_bomRev)
  AND ml.MaterialPartId = prd.ProductId
  AND prd.MaterialTypeId = mt.MaterialTypeId
  AND mt.MaterialTypeName = 'EPOXY';
  END IF;
  
  return(Result);
end Get_DAF_Vendor;

function Get_2071_Lamination(i_pnName in varchar2, i_Container In Varchar2)
return VARCHAR2 IS
  Result varchar2(3);

v_LotType          container.lottype%TYPE;
v_PackageCategory  a_Packagecategory.Packagecategoryname%TYPE;
v_DieEquivalent    INTEGER;
v_DieQty           INTEGER;
v_DieThickness     INTEGER;
  
BEGIN
SELECT co.lottype
INTO v_LotType
FROM container co
WHERE co.containername=i_Container;

SELECT pc.packagecategoryname, pt.dieequivalent, pt.dieqty, to_Number(replace(pt.diethickness,'mm',''))
INTO v_PackageCategory, v_DieEquivalent, v_DieQty, v_DieThickness
FROM product pt
INNER JOIN productbase ptb ON ptb.productbaseid=pt.productbaseid
INNER JOIN a_packagecategory pc ON pc.packagecategoryid=pt.packagecategoryid
WHERE ptb.productname=i_pnName
  AND ptb.revofrcdid=pt.productid;

IF v_LotType IN ('A','W') THEN
  RESULT := '(L)';
ELSIF i_pnName IN ('54-62-06924-008G') THEN
  RESULT := '';
--ELSIF i_pnName IN ('54-82-05010-016G') THEN
--  RESULT := '(L)';
ELSIF v_PackageCategory IN ('WAFER','MS-PROHG','SD-SIP','MICRO-SD','M2','ULGA','104LGA','52LGA',
'INAND2','INAND3','INAND4','INAND5','UBGA','INAND MCP','ISSD', 'ESSD','DAXTER','INAND6','INAND7' )
  AND v_DieEquivalent <= 4 AND v_DieThickness > 46 THEN
  RESULT := '(L)';
--ELSIF v_PackageCategory IN ('INAND2','INAND3','INAND4','INAND5','UBGA','INAND MCP','ISSD') 
--  AND v_DieEquivalent <= 2  AND v_DieThickness > 46 THEN
--  RESULT := '(L)';
ELSE
  RESULT := '';
END IF;    
  
  return(Result);
end Get_2071_Lamination;

End;
/
